﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using SinacorWebTools.Entity;

namespace SinacorWebTools.ResourceAccess
{
    public class Menu : DataAccess
    {
        public int IncluiMenu(Entity.Menu menu)
        {
            int linhasAfetadas = 0;
            DbConnection conn = CreateConnection();
            try
            {
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"INSERT INTO TFWMENU(
                                      CD_MENU,
                                      CD_MENUSUP,
                                      CD_FUNCAO, 
                                      CD_SISTEMA, 
                                      DS_ASSEMBLY,                                                                                          
                                      DS_CLASSE,                                                                                            
                                      IN_BLOQUEIO, 
                                      DS_MENU)
                                    VALUES
                                    (
                                      (SELECT MAX(CD_MENU) + 1 FROM TFWMENU),
                                      " + menu.CodigoMenuSuperior + @",
                                      '" + menu.Funcao.Sigla + @"', 
                                      '" + menu.CodigoSistema + @"', 
                                      '" + menu.DescricaoAssembly + @"',
                                      '" + menu.DescricaoClasse + @"',
                                      '" + menu.Bloqueado + @"',
                                      '" + menu.Descricao + "')";
                conn.Open();
                linhasAfetadas = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return linhasAfetadas;
        }

        public int AlteraMenu(Entity.Menu menu)
        {
            int linhasAfetadas = 0;
            conn = CreateConnection();
            DbCommand cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = @"UPDATE TFWMENU
                                  SET CD_MENUSUP = " + menu.CodigoMenuSuperior + @",
                                  CD_FUNCAO = '" + menu.Funcao.Sigla + @"', 
                                  CD_SISTEMA = '" + menu.CodigoSistema + @"', 
                                  DS_ASSEMBLY = '" + menu.DescricaoAssembly + @"',
                                  DS_CLASSE = '" + menu.DescricaoClasse + @"',
                                  IN_BLOQUEIO = '" + menu.Bloqueado + @"', 
                                  DS_MENU = '" + menu.Descricao + @"'
                                  WHERE CD_MENU = " + menu.Codigo;
                conn.Open();
                linhasAfetadas = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return linhasAfetadas;
        }

        public bool ExcluiMenu(int codigoMenu)
        {
            bool retorno = false;

            conn = CreateConnection();
            DbCommand cmd = conn.CreateCommand();

            try
            {
                cmd.CommandText = string.Format("DELETE TFWMENU WHERE CD_MENU = {0}", codigoMenu);

                conn.Open();
                cmd.ExecuteNonQuery();

                retorno = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }

            return retorno;
        }

        public SortableList<Entity.Menu> ListaMenus(string pesquisa, string modulo)
        {
            StringBuilder sql = new StringBuilder();

            sql.Append(@"SELECT DISTINCT
                                    M.CD_MENU,
                                    M.CD_MENUSUP,
                                    M.CD_FUNCAO,
                                    M.CD_SISTEMA,
                                    M.DS_ASSEMBLY,
                                    M.DS_CLASSE,
                                    M.IN_BLOQUEIO,
                                    M.DS_MENU,
                                    ME.DS_MENU AS DS_MENUSUP 
                        FROM        TFWMENU M 
                        INNER JOIN  TFWMENU ME ON M.CD_MENUSUP = ME.CD_MENU 
                        LEFT JOIN   TGSFCAO_SINACOR_MAIS FCAO ON TRIM(M.CD_FUNCAO) = TRIM(FCAO.TXT_SIGL_FCAO_SINACOR_MAIS) 
                        WHERE       1 = 1");

            if (modulo.Length > 0)
                sql.Append(@" AND UPPER(M.CD_SISTEMA) = '" + modulo + "'");

            if (pesquisa.Length > 0)
            {
                sql.Append(@" AND (UPPER(M.DS_MENU) LIKE '%" + pesquisa.ToUpper() + "%'");
                sql.Append(@" OR UPPER(M.DS_CLASSE) LIKE '%" + pesquisa.ToUpper() + "%'");
                sql.Append(@" OR UPPER(M.DS_ASSEMBLY) LIKE '%" + pesquisa.ToUpper() + "%'");
                sql.Append(@" OR UPPER(M.CD_FUNCAO) LIKE '%" + pesquisa.ToUpper() + "%')");
            }
            sql.Append(@" ORDER BY M.DS_MENU, M.DS_CLASSE");

            SortableList<Entity.Menu> menus = new SortableList<Entity.Menu>();

            conn = CreateConnection();
            try
            {
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = sql.ToString();
                conn.Open();

                List<IDataRecord> records = (from IDataRecord r in cmd.ExecuteReader()
                                             select r).ToList();

                menus = base.MapDataToBusinessEntityCollection<Entity.Menu>(records);

                cmd.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }

            return menus;
        }

        public SortableList<Entity.Menu> ListaMenuPorModulo(string siglaModulo)
        {
            SortableList<Entity.Menu> menus = new SortableList<Entity.Menu>();
            conn = CreateConnection();
            try
            {
                DbCommand cmd = conn.CreateCommand();
                cmd.CommandText = @"SELECT
                                        CD_MENU,
                                        DS_MENU || ' [' || TO_CHAR(CD_MENU) || ']' ""DS_MENU""
                                    FROM
                                        TFWMENU
                                    WHERE
                                        CD_SISTEMA = '" + siglaModulo + "' ORDER BY DS_MENU";
                conn.Open();

                using (IDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                        menus.Add(new Entity.Menu(Convert.ToInt32(dr["CD_MENU"]), dr["DS_MENU"].ToString()));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
            return menus;
        }
    }
}
